Generally, spreadsheet application programs are used to automate numerical and symbolic calculations for business, financial and scientific uses. Spreadsheet programs are the tools of choice for many business and analysis tasks because they combine a very usable graphical interface with a simple formula language that allows non-programmers, within the limits of the simple formula language, to create computational models.
Spreadsheet programs visually present numeric and non-numeric data in a two-dimensional grid for easy assimilation by the reader. Each element of the two-dimensional grid is referred to as a cell. A cell can contain either a data value, or it can contain a formula that calculates a new value based on the values of other cells. Spreadsheet cells that contain formulas are automatically recalculated when there are changes to the other cells that the formula depends upon. This mechanism allows a spreadsheet user to perform what-if scenarios only by modifying cell values and viewing or saving the effects of the changes.
Individual two-dimensional spreadsheets can be organized into a larger entity known as a notebook or workbook. The terms spreadsheet or worksheet will be used interchangeably herein, as will the terms notebook or workbook.
When worksheets are grouped together to form a workbook, the workbook and all of its worksheets are stored together as a single file (i.e. the workbook becomes the unit of storage and transfer when moving data between the program 's memory space and disk storage). Formulas stored in worksheet cells can reference other cells that are in worksheets in the same workbook and/or cells that are in worksheets in a different workbook. When formulas refer to a different workbook, however, known prior art spreadsheet programs cannot automatically recalculate formula values unless that other workbook has also been loaded into the program's memory space. These prior art spreadsheet programs do not allow multiple copies of the same workbook to be loaded into memory simultaneously and they do not allow multiple what-if scenarios to be calculated simultaneously.
In existing spreadsheet programs, data values can be stored in cells either by user input (directly or through a user-input formula) or by the user associating the cells with an external data source, such as a query to a database. When cells are associated with an external data source, the user of the program can control how often the external data source should be checked for changes. However, no mechanism exists to automatically detect when data referenced from the data source has changed and to recalculate the spreadsheet if, and only if, such a change has occurred.
Spreadsheets in prior art spreadsheet programs perform at most a single calculation with fixed inputs to arrive at a fixed set of answers. Changes in external data sources can affect the results of computations performed by a spreadsheet, but the computation itself is fixed. Prior art spreadsheet programs make the user vary cell values if the user wants to perform different calculations, including what-if scenarios.
Some prior art spreadsheet programs support various facilities for programmatic control over the spreadsheets to automate spreadsheet tasks. Some of these programs implement a macro recording and playback facility, which allows repetitive tasks to be automated by recording the user 's actions and later replaying them. Some programs publish application programming interfaces (APIs) that allow computer programs to be written to manipulate the spreadsheets or to extend the user interface of the program. Because programming skills are required to use these application programming interfaces, they are not used by most spreadsheet users.
Some prior art spreadsheet programs cache internally the results of intermediate calculations (cell values) to improve the speed of recalculation. Prior art spreadsheet programs support re-execution of external data queries either when manually requested, on a periodic basis while the workbook is loaded into the program 's memory, or whenever the workbook is opened. None of these methods are optimal, and none of these methods are effective when the spreadsheet program is not running.